package com.ben.restjpademo.service;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import com.ben.restjpademo.dao.ShowVulsRepository;
import com.ben.restjpademo.dao.ShowVulsTestRepository;
import com.ben.restjpademo.dao.VullevelRepository;
import com.ben.restjpademo.dao.VultypeRepository;
import com.ben.restjpademo.domain.ShowVuls;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartHttpServletRequest;

import javax.annotation.Resource;
import javax.persistence.criteria.*;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Controller
@RequestMapping("api/showvuls")
public class ShowVulsController {
    @Resource
    ShowVulsRepository showVulsRepository;

    @Resource
    VultypeRepository vultypeRepository;

    @Resource
    VullevelRepository vullevelRepository;

    @Resource
    ShowVulsTestRepository showVulsTestRepository;





    private String url="";
    private String title="";
    private String cveid="";
    private String publishtime="";
    private String updatetime="";
    private String effectsys="";
    private String message="";
    private String poc="";
    private String vendorpatch="";
    private Integer typeid=1;
    private Integer levelid=1;
    private Pageable pageable;

    public Specification getSpecification() {
        return new Specification() {

            @Override
            public Predicate toPredicate(Root root, CriteriaQuery query, CriteriaBuilder cb) {
                List<Predicate> predicates = new ArrayList<>();
                if (StringUtils.isNotBlank(url)) {
                    predicates.add(cb.like(root.get("url"), "%" + url + "%"));
                }
                if (StringUtils.isNotBlank(title)) {
                    predicates.add(cb.like(root.get("title"), "%" + title + "%"));
                }
                if (StringUtils.isNotBlank(cveid)) {
                    predicates.add(cb.like(root.get("cveId"), "%" + cveid + "%"));
                }
                if (StringUtils.isNotBlank(publishtime)) {
                    predicates.add(cb.like(root.get("publishTime"), "%" + publishtime + "%"));
                }
                if (StringUtils.isNotBlank(updatetime)) {
                    predicates.add(cb.like(root.get("updateTime"), "%" + updatetime + "%"));
                }
                if (StringUtils.isNotBlank(effectsys)) {
                    predicates.add(cb.like(root.get("effectSys"), "%" + effectsys + "%"));
                }
                if (StringUtils.isNotBlank(message)) {
                    predicates.add(cb.like(root.get("message"), "%" + message + "%"));
                }
                if (StringUtils.isNotBlank(poc)) {
                    predicates.add(cb.like(root.get("poc"), "%" + poc + "%"));
                }
                if (StringUtils.isNotBlank(vendorpatch)) {
                    predicates.add(cb.like(root.get("vendorPatch"), "%" + vendorpatch + "%"));
                }

                if (typeid != null && !vultypeRepository.findById(typeid).getType().equals("所有类型")) {
                    predicates.add(cb.equal(root.<Integer>get("typeid"), typeid));
                }
                if (levelid != null && !vullevelRepository.findById(levelid).getLevel().equals("所有程度")) {
                    predicates.add(cb.equal(root.<Integer>get("levelid"), levelid));
                }
                return cb.and(predicates.toArray(new Predicate[predicates.size()]));
            }
        };
    }



    @ResponseBody
    @RequestMapping(value="/getvuls/page={page}&size={size}&sort={sort}",method=RequestMethod.GET)
    public Page<ShowVuls> findall(@PathVariable("page") int page, @PathVariable("size") int size, @PathVariable("sort") String sorts){
        this.url="";
        this.title="";
        this.cveid= "";
        this.publishtime= "";
        this.updatetime= "";
        this.effectsys="";
        this.message="";
        this.poc= "";
        this.vendorpatch = "";
        this.typeid = 1;
        this.levelid = 1;
        Sort sort=new Sort(Sort.Direction.DESC,sorts);
        Pageable pageable=new PageRequest(page,size,sort);
        Page<ShowVuls> vulList= showVulsRepository.findAll(pageable);

//        for ( ShowVuls vul : vulList) {
//            System.out.println(vul.getTitle());
//        }

        return vulList;
    }

    @ResponseBody
    @RequestMapping(value = "/getvul/id={id}",method = RequestMethod.GET)
    public ArrayList<ShowVuls> getvulbyvulid(@PathVariable("id") int id){
        ArrayList<ShowVuls> vul=showVulsRepository.getvuldetailbyvulid(id);
        return vul;
    }
    @ResponseBody
    @RequestMapping(value = "/getvul/type={type}",method = RequestMethod.GET)
    public List<ShowVuls> findListbyType(@PathVariable("type") String type) {

        List<ShowVuls> vul = showVulsRepository.findByVultype_Type(type);
        return vul;
    }


    @ResponseBody
    @RequestMapping(value="/getvul",method=RequestMethod.GET)

    public Page<ShowVuls> findList(@RequestParam(value ="url")String url,
                                   @RequestParam(value="title") String title,
                                   @RequestParam(value="cveid") String cveid,
                                   @RequestParam(value="publishtime") String publishtime,
                                   @RequestParam(value="updatetime") String updatetime,
                                   @RequestParam(value="effectsys") String effectsys,
                                   @RequestParam(value="message") String message,
                                   @RequestParam(value="poc") String poc,
                                   @RequestParam(value="vendorpatch") String vendorpatch,
                                   @RequestParam(value="levelid") Integer levelid,
                                   @RequestParam(value="typeid") Integer typeid,

                                   @RequestParam(value="page") Integer page,
                                   @RequestParam(value="size") Integer size,
                                   @RequestParam(value="sort") String sorts){

//        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
//        java.util.Date updateTime = format.parse(updatetime);
//        java.util.Date publishTime = format.parse(publishtime);
//        Date pTime =  Date(publishTime.getTime());
//        Date uTime = Date(updateTime.getTime());
        this.url=url;
        this.title=title;
        this.cveid= cveid;
        this.publishtime= publishtime;
        this.updatetime= updatetime;
        this.effectsys=effectsys;
        this.message=message;
        this.poc = poc;
        this.vendorpatch = vendorpatch;
        this.typeid = typeid;
        this.levelid = levelid;

        //System.out.println(this.levelid);
        //System.out.println(this.typeid);

        Sort sort=new Sort(Sort.Direction.DESC,sorts);
        this.pageable=new PageRequest(page,size,sort);

        Specification specification = this.getSpecification();

        Page<ShowVuls>  vulList = showVulsTestRepository.findAll(specification, pageable);


        return vulList;
    }


    @RequestMapping(value = "/toExcel",method = RequestMethod.GET )
    public void toExcel(HttpServletResponse response ) {
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
        Integer exceltypeid;
        Integer excellevelid;

        if (vultypeRepository.findById(typeid).getType().equals("所有类型")) {
            exceltypeid = null;
        }
        else{
            exceltypeid =this.typeid;
        }
        if (vullevelRepository.findById(levelid).getLevel().equals("所有程度")) {
            excellevelid = null;
        }
        else {
             excellevelid =this.levelid;
        }

        Integer templevid = this.levelid;
        Integer temptypid = this.typeid;
        Specification specification = this.getSpecification();

        List<ShowVuls> vullist = showVulsTestRepository.findAll(specification);

        for (ShowVuls vul: vullist){
            if(vul.getPoc()!=null && vul.getPoc().length()>32767){
                vul.setPoc(vul.getPoc().substring(0,32767));
            }
            if(vul.getVendorPatch()!=null && vul.getVendorPatch().length()>32767) {
                vul.setVendorPatch(vul.getVendorPatch().substring(0, 32767));
            }
            if(vul.getMessage()!=null &&vul.getMessage().length()>32767) {
                vul.setMessage(vul.getMessage().substring(0, 32767));
            }
            if(vul.getEffectSys()!=null &&vul.getEffectSys().length()>32767) {
                vul.setEffectSys(vul.getEffectSys().substring(0,32767));
            }
        }
        String type = vultypeRepository.findById(temptypid).getType();
        String level = vullevelRepository.findById(templevid).getLevel();
        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(String.format(title="类型为 %s,危害程度为 %s 的漏洞", type,level),"漏洞"),
                ShowVuls.class, vullist);
        if (workbook != null){
            java.util.Date date = new java.util.Date();
            df = new SimpleDateFormat(
                    "yyyy-MM-dd");// 设置日期格式
            String nowTime = df.format(date);
            downLoadExcel("latest_vul_"+nowTime+".xls", response, workbook);

        }


    }


    private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            throw new RuntimeException(e.getMessage());
        }
    }

    @ResponseBody
    @RequestMapping(value="/{id}",method=RequestMethod.DELETE)
    public ShowVuls deleteVuls(@PathVariable("id") int id){
        ShowVuls vul = showVulsRepository.findById(id);
        showVulsRepository.delete(vul);
        return vul;
    }

    @ResponseBody
    @RequestMapping(value="/{id}", method=RequestMethod.PUT)
    /**/
    public ShowVuls updateVuls(@PathVariable("id") Integer id,@RequestBody ShowVuls showVuls) {
        ShowVuls vul = showVulsRepository.findById(id);
        vul.setChinese(showVuls.getChinese());
        vul.setCveId(showVuls.getCveId());
        vul.setEffectSys(showVuls.getEffectSys());
        vul.setMessage(showVuls.getMessage());
        vul.setPublishTime(showVuls.getPublishTime());
        vul.setUpdateTime(showVuls.getUpdateTime());
        vul.setPoc(showVuls.getPoc());
        vul.setUrl(showVuls.getUrl());
        vul.setVendorPatch(showVuls.getVendorPatch());
        showVulsRepository.save(vul);
        return vul;

    }




    /**
     * Java代码实现MySQL数据库导出
     *
     * @author GaoHuanjie
     * @param hostIP MySQL数据库所在服务器地址IP
     * @param userName 进入数据库所需要的用户名
     * @param password 进入数据库所需要的密码
     * @param savePath 数据库导出文件保存路径
     * @param fileName 数据库导出文件文件名
     * @param databaseName 要导出的数据库名
     * @return 返回true表示导出成功，否则返回false。
     */
    private  boolean exportDatabaseTool(String hostIP, String userName, String password, String savePath, String fileName, String databaseName) throws InterruptedException {
        File saveFile = new File(savePath);
        if (!saveFile.exists()) {// 如果目录不存在
            saveFile.mkdirs();// 创建文件夹
        }
        if(!savePath.endsWith(File.separator)){
            savePath = savePath + File.separator;
        }

        PrintWriter printWriter = null;
        BufferedReader bufferedReader = null;
        try {
            printWriter = new PrintWriter(new OutputStreamWriter(new FileOutputStream(savePath + fileName), "utf8"));
            Process process = Runtime.getRuntime().exec(" mysqldump -h" + hostIP + " -u" + userName + " -p" + password + " --set-charset=UTF8 " + databaseName);
            InputStreamReader inputStreamReader = new InputStreamReader(process.getInputStream(), "utf8");
            bufferedReader = new BufferedReader(inputStreamReader);
            String line;
            while((line = bufferedReader.readLine())!= null){
                printWriter.println(line);
            }
            printWriter.flush();
            if(process.waitFor() == 0){//0 表示线程正常终止。
                return true;
            }
        }catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (bufferedReader != null) {
                    bufferedReader.close();
                }
                if (printWriter != null) {
                    printWriter.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return false;
    }


    @RequestMapping(value="/download", method=RequestMethod.GET)
    public String downloadFile(HttpServletRequest request, HttpServletResponse response) {
        try{
            if (exportDatabaseTool("localhost", "root", "123456", ".", "vuls.sql", "mydemo")) {
                System.out.println("数据库成功备份！！！");
            }
            else {
                System.out.println("数据库备份失败！！！");
            }
        }
        catch (InterruptedException e) {
            e.printStackTrace();
        }
        String fileName = "vuls.sql";// 文件名
        if (fileName != null) {
            //设置文件路径
            File file = new File("./vuls.sql");

            //File file = new File(realPath , fileName);
            if (file.exists()) {

                response.setContentType("application/force-download");// 设置强制下载不打开
                response.addHeader("Content-Disposition", "attachment;fileName=" + fileName);// 设置文件名
                byte[] buffer = new byte[65535];
                FileInputStream fis = null;
                BufferedInputStream bis = null;
                try {
                    fis = new FileInputStream(file);
                    bis = new BufferedInputStream(fis);
                    OutputStream os = response.getOutputStream();
                    int i = bis.read(buffer);
                    while (i != -1) {
                        os.write(buffer, 0, buffer.length);
                        os.flush();
                        i = bis.read(buffer);
                    }
                    return "下载成功";
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    if (bis != null) {
                        try {
                            bis.close();
                        } catch (IOException e) {
                            e.printStackTrace();
                        }
                    }
                    if (fis != null) {
                        try {
                            fis.close();
                        } catch (IOException e) {
                            e.printStackTrace();
                        }
                    }
                }
            }
        }
        return "下载失败";
    }

    @ResponseBody
    @RequestMapping(value="/upload",method=RequestMethod.POST)
    @CrossOrigin
    public boolean testUploadFile(HttpServletRequest req,HttpServletResponse response,MultipartHttpServletRequest multiReq) throws IOException{
        response.setHeader("Access-Control-Allow-Origin", "*");
        try{
            String fileName = multiReq.getFile("file").getOriginalFilename();
            // System.out.println(fileName);


            FileOutputStream fos=new FileOutputStream(new File(fileName));
            FileInputStream fs=(FileInputStream) multiReq.getFile("file").getInputStream();
            byte[] buffer=new byte[65535];
            int len=fs.read(buffer);
            while(len!=-1){
                fos.write(buffer, 0, buffer.length);
                fos.flush();
                len=fs.read(buffer);
            }


            fos.close();
            fs.close();
            Process process = null;
            List<String> processList = new ArrayList<String>();
            try {
                List<String> cmds = new ArrayList<String>();
                cmds.add("sh");
                cmds.add("-c");
                cmds.add("mysql -uroot -p123456 mydemo < "+fileName);

                //process = Runtime.getRuntime().exec("ls -l | wc");
                ProcessBuilder pb = new ProcessBuilder(cmds);
                process = pb.start();

                BufferedReader input = new BufferedReader(new InputStreamReader(process.getInputStream()));
                String line = "";
                while ((line = input.readLine()) != null) {
                    processList.add(line);
                }
                input.close();
            } catch (IOException e) {
                e.printStackTrace();
            }

            for (String line : processList) {
                System.out.println(line);
            }
            return true;
        }
        catch (Exception e) {
            e.printStackTrace();
            return false;
        }




    }



}
